#!/use/bin/python
# coding=utf-8
# 主动服务 开机率 analysis_startup_rate
import datetime
import pymysql

from dbutils.pooled_db import PooledDB



# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur


def getMac():
    try:
        conn, cur = getConfConnection()
        conn2, cur2 = getDataConnection()
        # 获取 2.2.1版本的aaid
        selectAaidSql = f"""select t.aa_id from device_analysis t where t.appVerName ='2.2.1' limit 100"""
        cur2.execute(selectAaidSql)
        selectResult = cur2.fetchall()
        selectMacSql = "select t.mac from life_assistant_devices t  where 1=1"
        delNum = 1
        dataStr = ""
        #5千条设备查询一下
        for result in selectResult:
            id = result[0]
            dataStr = f"{dataStr} '{str(id)}',"
            delNum  = delNum+1
            if delNum>5000:
                selectMacSql2 = f"{selectMacSql} and aa_id in ( {dataStr[:-1]} )"
                cur.execute(selectMacSql2)
                selectResult = cur.fetchall()
                for result in selectResult:
                    print(result[0])
                delNum = 1
                dataStr= ""
        #剩余的数据
        updateSql2 = f"{selectMacSql} and aa_id in ( {dataStr[:-1]} )"
        print(updateSql2)
        cur.execute(updateSql2)
        selectResult = cur.fetchall()
        for result in selectResult:
            print(result[0])
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

def getYjylUi():
    try:
        conn, cur = getConfConnection()
        conn2, cur2 = getDataConnection()
        batch_size = 300000
        total_count = 4500000
        switchAllAll = 0
        for i in range(0, total_count, batch_size):
            # 获取 2.2.1版本的aaid
            selectAaidSql = f"""select t.aa_id from device_analysis t where t.appVerName ='2.1.2' LIMIT {i}, {batch_size}"""
            cur2.execute(selectAaidSql)
            print(selectAaidSql)
            selectResult = cur2.fetchall()
            selectMacSql = "select count(1) from life_assistant_devices t  where 1=1"
            delNum = 1
            dataStr = ""
            switchAll = 0;
            #5千条设备查询一下
            for result in selectResult:
                id = result[0]
                dataStr = f"{dataStr} '{str(id)}',"
                delNum  = delNum+1
                if delNum>5000:
                    selectMacSql2 = f"{selectMacSql} and (switches_state1 & (1 << 3 - 1))<>0 and aa_id in ( {dataStr[:-1]} )"
                    cur.execute(selectMacSql2)
                    #print(selectMacSql2)
                    selectResult = cur.fetchone()
                    switch = selectResult[0]
                    switchAll = switchAll+switch
                    #print(f"个数为:{switch}")
                    delNum = 1
                    dataStr= ""

            print(f"个数为:{switchAll}")
            switchAllAll = switchAllAll + switchAll
        print(f"总个数为:{switchAllAll}")
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


if __name__ == '__main__':
    #getMac()
    getYjylUi()